package com.gjun.struts.resource;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.FileSystemXmlApplicationContext;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

import com.gjun.struts.bean.GjunsystemPrivilege;
import com.gjun.struts.bean.GjunsystemRole;
import com.gjun.struts.bean.GjunsystemUser;
import com.gjun.struts.bean.GjunsystemUserJsonBean;
import com.gjun.struts.bean.GjunsystemUserJsonBean2;
import com.gjun.struts.util.MD5;

public class GjunSystemResourceUserDAO extends HibernateDaoSupport implements
		GjunSystemUserResourceIf {

	public int loadUserCountByConditionForName(final String username) {
		Long count = (Long)this.getHibernateTemplate().execute(new HibernateCallback(){
			public Object doInHibernate(Session session) throws HibernateException, SQLException {
				// TODO Auto-generated method stub
				Query q = session.createQuery("select count(*) from GjunsystemUser u where u.username like ?");
				q.setParameter(0, "%"+username+"%");
				
				return q.uniqueResult();
			}
		});
		return count.intValue();
	}
	
	public List<GjunsystemUserJsonBean2> loadUserByConditionForName(final String username,final int start,final int max) {
		// TODO Auto-generated method stub
		
		final List<GjunsystemUserJsonBean2> beans = new ArrayList<GjunsystemUserJsonBean2>();
		final List<GjunsystemUser> users = (List<GjunsystemUser>)this.getHibernateTemplate().execute(new HibernateCallback(){
			public Object doInHibernate(Session session) throws HibernateException, SQLException {
				// TODO Auto-generated method stub
				Query q = session.createQuery("from GjunsystemUser u where u.username like ?");
				q.setParameter(0, "%"+username+"%");
				q.setFirstResult(start);
				q.setMaxResults(max);
				
				return q.list();
			}
		});
		
		if(users!=null && users.size()>0){
			
			this.getHibernateTemplate().execute(new HibernateCallback(){
				public Object doInHibernate(Session session) throws HibernateException, SQLException {
					for(GjunsystemUser u : users){
						
						GjunsystemUserJsonBean2 jsonbean = new GjunsystemUserJsonBean2();
						Query q = session.createQuery("select r from GjunsystemUser u left join u.roles r where u.userid=? ");
						q.setParameter(0, u.getUserid());
						List<GjunsystemRole> userroles = q.list();
						String str="";
						if(userroles!=null && userroles.size()>0){
							for(int i=0;i<userroles.size();i++){
								
								GjunsystemRole r = userroles.get(i);
								if(r!=null)
								str+=r.getRolename()+((i<userroles.size() && i!=0)?" , ":"");
							}
						}
						jsonbean.setUserid(u.getUserid());
						jsonbean.setRegdate(u.getRegdate());
						jsonbean.setState(u.getState());
						jsonbean.setUsername(u.getUsername());
						jsonbean.setRolenameList(str);
						beans.add(jsonbean);
						
					}
					
					
					return null;
				}
			});
		}
		
		return beans;
	}
	public void updateSystemUserInfo(final int id, final String passwd, final String tel, final String qq, final String email) throws Exception {
		// TODO Auto-generated method stub
		this.getHibernateTemplate().execute(new HibernateCallback(){
			public Object doInHibernate(Session session) throws HibernateException, SQLException {
				// TODO Auto-generated method stub
				Query q = session.createQuery("update GjunsystemUser u set u.passwd=?,u.tel=?,u.qq=?,u.email=? where u.userid=?");
				q.setParameter(0, passwd);
				q.setParameter(1, tel);
				q.setParameter(2, qq);
				q.setParameter(3, email);
				q.setParameter(4, id);
				return q.executeUpdate();
			}
		});
	}
	public boolean loadOtherUserExistEmail(final String email, final int id) {
		Long count = (Long)this.getHibernateTemplate().execute(new HibernateCallback(){
			public Object doInHibernate(Session session) throws HibernateException, SQLException {
				// TODO Auto-generated method stub
				Query q = session.createQuery("select count(*) from GjunsystemUser u where u.email=? and u.userid!=?");
				q.setParameter(0, email);
				q.setParameter(1, id);
				return q.uniqueResult();
			}
		});
		if(count>0) return true;
		return false;
	}
	public boolean loadExistByEmail(final String email) {
		Long count = (Long)this.getHibernateTemplate().execute(new HibernateCallback(){
			public Object doInHibernate(Session session) throws HibernateException, SQLException {
				// TODO Auto-generated method stub
				Query q = session.createQuery("select count(*) from GjunsystemUser u where u.email=?");
				q.setParameter(0, email);
				return q.uniqueResult();
			}
		});
		if(count>0) return true;
		return false;
	}
	public boolean loadUserExistByName(final String name) {
		Long count = (Long)this.getHibernateTemplate().execute(new HibernateCallback(){
			public Object doInHibernate(Session session) throws HibernateException, SQLException {
				// TODO Auto-generated method stub
				Query q = session.createQuery("select count(*) from GjunsystemUser u where u.username=?");
				q.setParameter(0, name);
				return q.uniqueResult();
			}
		});
		if(count>0) return true;
		return false;
	}
	public List<GjunsystemUserJsonBean> loadAllUsersToJsonBean() {
		return (List<GjunsystemUserJsonBean>) this.getHibernateTemplate().execute(
				new HibernateCallback() {
					public Object doInHibernate(Session session)
							throws HibernateException, SQLException {
						// TODO Auto-generated method stub
						Query query = session
								.createQuery("select new com.gjun.struts.bean.GjunsystemUserJsonBean(u.userid,u.username) from GjunsystemUser u where u.state=1");

						return query.list();
					}
				});
	}
	public boolean updateSystemUserState(final String username, final String code) throws Exception{
		GjunsystemUser user = (GjunsystemUser) this.getHibernateTemplate()
				.execute(new HibernateCallback() {
					public Object doInHibernate(Session session)
							throws HibernateException, SQLException {
						Query q = session.createQuery("from GjunsystemUser user where user.username=? and user.code=? and user.state=0");
						q.setString(0,username);
						q.setString(1,code);
						return q.uniqueResult();
					}
				});
		if(user==null){
			return false;
		}else{
			user.setState(1);
			return true;
		}
		
		
		
		
	}

	public void saveSystemUser(GjunsystemUser user, final String[] roles)
			throws Exception {
		// TODO Auto-generated method stub
		List<GjunsystemRole> useRoles = (List<GjunsystemRole>) this
				.getHibernateTemplate().execute(new HibernateCallback() {
					public Object doInHibernate(Session session)
							throws HibernateException, SQLException {
						// TODO Auto-generated method stub

						if (roles != null && roles.length > 0) {
							String str = "";
							for (int i = 0; i < roles.length; i++) {
								str += roles[i]
										+ ((i == roles.length - 1) ? "" : ",");
							}

							Query q = session
									.createQuery("from GjunsystemRole gr where gr.roleid in ("
											+ str + ")");
							return q.list();
						}
						return null;

					}
				});

		if (useRoles != null) {
			user.getRoles().addAll(useRoles);
		}
		this.getHibernateTemplate().save(user);
	}

	public GjunsystemUser loadLoginAdmin(final String name, final String passwd)
			throws Exception {
		return (GjunsystemUser) this.getHibernateTemplate().execute(
				new HibernateCallback() {
					public Object doInHibernate(Session session)
							throws HibernateException, SQLException {
						// TODO Auto-generated method stub
						Query query = session
								.createQuery("from GjunsystemUser admin where admin.username=? "
										+ "and admin.passwd=?");
						query.setString(0, name);
						query.setString(1, passwd);
						return query.uniqueResult();
					}
				});
	}

	public List<GjunsystemPrivilege> loadUserPrivilegesByUserId(final int userid) {
		return (List<GjunsystemPrivilege>) this.getHibernateTemplate().execute(
				new HibernateCallback() {
					public Object doInHibernate(Session session)
							throws HibernateException, SQLException {
						// TODO Auto-generated method stub
						Query query = session
								.createQuery("select distinct pri from GjunsystemUser admin "
										+ " left join admin.roles role left join"
										+ " role.privileges pri where admin.userid="
										+ userid);

						return query.list();
					}
				});
	}

	public static void main(String[] args) {
		ApplicationContext context = new FileSystemXmlApplicationContext(
				"src/applicationContext.xml");
		// BeanFactory factory = new XmlBeanFactory(new
		// ClassPathResource("applicationContext.xml"));
		GjunSystemUserResourceIf dao = (GjunSystemUserResourceIf) context
				.getBean("systemUserDAO");

		int count = dao.loadUserCountByConditionForName("client");
		System.out.println(count);
		
		
		List<GjunsystemUserJsonBean2> beans = dao.loadUserByConditionForName("admin",0,10);
		for(GjunsystemUserJsonBean2 bean : beans){
			System.out.println(bean.getUserid()+"\t"+bean.getUsername()+"\t"+bean.getRegdate_str()+"\t"+bean.getState()+"\t"+bean.getRolenameList());
		}
		
	}

}
